Quick PC 61/Quick PC 61.iso/I386/SQLRUN.CAB/sp1ServUni.sql.6EAD8B17_4094_442C_BF0E_93C639158E73

Text (UTF-16) | 2001-04-13 | 137.4 KB | 2,307 lines

/*------------------------------------------------------------------------------

SP1_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 8.0 to SP1.

Changes in this file are organized as follows (please maintain):
    System Tables (UPGRADE.SQL)
    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
    Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
    SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
    Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
    Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
    Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--    System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
    where error in
    (15355, 21074, 21396
    ,8668, 8669
    ,13089, 13090, 13091
    ,7619
    ,1959, 15248
    ,21513
    ,21512
    ,21515, 21516, 21517, 21518, 21519, 21520
    ,14359
    ,8526
)
go

-- 21396 has no corresponding new message.  It has been replaced with an updated 21074

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (15355 ,10 ,0 ,'''sys'' will be a reserved user or role name in next version of SQL Server.', 1033)
insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21074,16,0, 'The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8668,16 ,0 ,'An index cannot be created on the view ''%.*ls'' because the select list of the view contains a non-aggregate expression.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8669,16 ,0 ,'The indexed view ''%.*ls'' is not updatable.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (13089 ,10 ,0 ,'lock' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (13090 ,10 ,0 ,'thread' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (13091 ,10 ,0 ,'communication buffer' ,1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid) values
        (7619, 16, 0, 'Execution of a full-text operation failed. %ls', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (1959, 16 ,0 ,'Cannot create an index on a view or computed column because the compatibility level of this database is less than 80.' ,1033)
insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (15248 ,11 ,0 ,'Either the parameter @objname is ambiguous or the claimed @objtype (%s) is wrong.' ,1033)
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21513,18,0,'Foreign key column ''%s'' cannot be excluded from a vertical partition.', 1033)
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21512,18,0,'%ls: The %ls parameter is shorter than the minimum required size.', 1033)
GO

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21515,18,0,'Replication custom procedures will not be scripted because the specified publication ''%s'' is a snapshot publication.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21516,10,0,'Transactional replication custom procedures for publication ''%s'' from database ''%s'':', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21517,10,0,'Replication custom procedures will not be scripted for article ''%s'' because the auto-generate custom procedures schema option is not enabled.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21518,0,0,'Replication custom procedures for article ''%s'':', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21519,10,0,'Custom procedures will not be scripted for article update commands based on direct INSERT, UPDATE, or DELETE statements.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (21520,10,0,'Custom procedure will not be scripted because ''%s'' is not a recognized article update command syntax.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (14359, 16, 0, 'Active Directory is either not enabled on the network or not supported by the operating system.', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8526, 16, 0, 'Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.', 1033)
go
--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------
update spt_values
    set low = -2147483648
    where number = 1535 and type = 'C'

update spt_values
    set low = -2147483648
    where number = 1549 and type = 'C'

if object_id('sp_helptext','P') IS NOT NULL
    drop procedure sp_helptext
if object_id('sp_droplogin','P') IS NOT NULL
    drop procedure sp_droplogin
if object_id('sp_addsrvrolemember','P') IS NOT NULL
    drop procedure sp_addsrvrolemember
if object_id('sp_dropsrvrolemember','P') IS NOT NULL
    drop procedure sp_dropsrvrolemember
if object_id('sp_grantdbaccess','P') IS NOT NULL
    drop procedure sp_grantdbaccess
if object_id('sp_addalias','P') IS NOT NULL
    drop procedure sp_addalias
if object_id('sp_addrole','P') IS NOT NULL
    drop procedure sp_addrole
if object_id('sp_addapprole','P') IS NOT NULL
    drop procedure sp_addapprole
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
if object_id('sp_fulltext_table','P') IS NOT NULL
    drop procedure sp_fulltext_table
if object_id('sp_fulltext_column','P') IS NOT NULL
    drop procedure sp_fulltext_column
go

create procedure sp_droplogin
    @loginame sysname
as

declare @exec_stmt nvarchar(890)

    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare    @sid    varbinary(85)

    /*Create temp tables before any DML to ensure dynamic*/
    -- CREATE TEMPORARY TABLES FOR LATER USE --
       create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null)
    create table #retval (job_count int not null)

    -- CHECK PERMISSIONS --
    IF (not is_srvrolemember('securityadmin') = 1)
    begin
       dbcc auditevent (104, 2, 0, @loginame, NULL, NULL, NULL)
       raiserror(15247,-1,-1)
       return (1)
    end
    ELSE
    begin
       dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_droplogin')
        return (1)
    end

    -- VALIDATE LOGIN NAME (SQL LOGIN) --
    select @sid = sid from master.dbo.syslogins
        where loginname = @loginame and isntname = 0
    if (@sid is null)
    begin
        raiserror(15007,10,-1,@loginame)
        return(1)
    end
    -- CANNOT CHANGE SA ROLES --
    else if @sid = 0x1    -- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) --
    if exists(select * from master.dbo.sysprocesses where sid = @sid and status != 'dormant')
    begin
        raiserror(15434, -1, -1, @loginame)
        return(1)
    end

    -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN --
    if exists(select * from master.dbo.sysdatabases where sid = @sid)
    begin
        raiserror(15174, -1, -1, @loginame)
        select 'Databases owned by login:' = name
                from master.dbo.sysdatabases where sid = @sid
        return(1)
    end

    -- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --
    declare @dbname        sysname
    declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
    open ms_crs_dbname
    fetch ms_crs_dbname into @dbname
    while @@fetch_status >= 0
    begin
        if (has_dbaccess(@dbname) = 1)
        begin
            select @exec_stmt = 'use ' + quotename( @dbname , '[') + '
                   insert into #db_list (dbname, user_name)
                select N'+ quotename( @dbname , '''')+', name from sysusers
                where sid = suser_sid(N' + quotename( @loginame , '''') + ') '
            exec (@exec_stmt)
        end
        else
            raiserror(15622,-1,-1, @dbname)

        fetch ms_crs_dbname into @dbname
    end
    deallocate ms_crs_dbname

    -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --
    if (select count(*) from #db_list) <> 0
    begin
        raiserror(15175,-1,-1,@loginame)
        select
            'Database name:' = dbname,
            'User name:' = user_name,
            'Mapping type:' = 'user'
        from #db_list
        order by dbname
        return (1)
    end

    -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN --
    if db_id('msdb') is not null
        and object_id('msdb.dbo.sp_check_for_owned_jobs') is not null
    begin
        exec msdb.dbo.sp_check_for_owned_jobs @loginame, '#retval'
        if exists (select job_count from #retval where job_count > 0)
        begin
            declare @job_count int
            select @job_count = job_count from #retval
            raiserror(14248, -1, -1, @job_count)
            return (1)
        end
    end

    -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --
    delete from master.dbo.sysxlogins where sid = @sid

    -- FINALIZATION: SUCCESS/FAILURE MESSAGE
    if @@rowcount > 0
    begin
        -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
        exec('use master grant all to null')

        raiserror(15479,-1,-1)
        return (0)
    end
    else
    begin
        raiserror(15007,10,-1,@loginame)
        return (1)
    end     -- sp_droplogin
go

create procedure sp_addsrvrolemember
    @loginame sysname,            -- login name
    @rolename sysname = NULL    -- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
            @sid        varbinary(85)

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_addsrvrolemember')
        return (1)
    end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
        dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
    begin
        dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15247,-1,-1)
        return (1)
    end

    -- AUDIT A SUCCESSFUL SECURITY CHECK --
    dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
            WHEN 'bulkadmin'        THEN 4096
            ELSE NULL END

    select @sid = sid from master.dbo.syslogins where loginname = @loginame
    -- ADD ROW FOR NT LOGIN IF NEEDED --
    if @sid is null
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
        begin
            raiserror(15007,-1,-1,@loginame)
            return (1)
        end
    end
    -- CANNOT CHANGE SA ROLES --
    else if @sid = 0x1    -- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

    -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
    exec('use master grant all to null')

    raiserror(15488,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
    return (@@error) -- sp_addsrvrolemember
go

create procedure sp_dropsrvrolemember
    @loginame sysname,            -- login name
    @rolename sysname = NULL    -- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
            @sid        varbinary(85)

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_dropsrvrolemember')
        return (1)
    end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
        dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
    begin
        dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15247,-1,-1)
        return (1)
    end

    -- AUDIT THE SUCCESSFUL SECURITY CHECK --
    dbcc auditevent (108, 2, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
            WHEN 'bulkadmin'        THEN 4096
            ELSE NULL END

    select @sid = sid from master.dbo.syslogins where loginname = @loginame
    -- ERROR IF USER DOESNT EXIST --
    if @sid is null
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end
    -- CANNOT CHANGE SA ROLES --
    else if @sid = 0x1    -- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus & ~@rolebit, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

    -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
    exec('use master grant all to null')

    raiserror(15489,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
    return (@@error) -- sp_dropsrvrolemember
go

create procedure sp_grantdbaccess
    @loginame       sysname,
    @name_in_db     sysname = NULL OUT
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @sid        varbinary(85),
            @status     smallint

    if @name_in_db is null
        select @name_in_db = @loginame

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
        dbcc auditevent (109, 3, 0, @loginame, @name_in_db, NULL, NULL)
        raiserror(15247,-1,-1)
        return (1)
    end
    else
    begin
        dbcc auditevent (109, 3, 1, @loginame, @name_in_db, NULL, NULL)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_grantdbaccess')
        return (1)
    end

    -- VALIDATE NAME-IN-DB --
    if @name_in_db <> @loginame
    begin
        exec @ret = sp_validname @name_in_db
        if @ret <> 0
            return(1)
        if (charindex('\', @name_in_db) > 0)
        begin
            raiserror(15006,-1,-1,@name_in_db)
            return (1)
        end
    end

    -- CHECK FOR SPECIAL USER GUEST --
    if @name_in_db = 'guest'
    begin
        -- ERROR IF NOT USER, OR ALREADY ADDED --
        if @loginame <> 'guest'
        begin
            raiserror(15062,-1,-1)
            return(1)
        end
        if exists (select * from sysusers where hasdbaccess = 1 and name = 'guest')
        begin
            raiserror(15023,-1,-1,'guest')
            return (1)
        end

        -- ENABLE USER GUEST --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where name = 'guest'
        return (0)
    end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = case when (charindex('\', @loginame) <> 0) then 4 else 0 end
    if @status = 0
        select @sid = sid from master.dbo.syslogins         -- sql user
            where isntname = 0 and loginname = @loginame
    if @sid is null
    begin
        -- NT GROUPS REQUIRE DOMAIN NAME --
        if @status = 4
            select @sid = get_sid('\G'+@loginame, NULL)     -- nt group
        if @sid is null
        begin
            select @sid = get_sid('\U'+@loginame, NULL)     -- nt user
            if @sid is not null
                select @status = 12
        end
    end
    -- PREVENT USE OF CERTAIN LOGINS --
    else if @sid = 0x1    -- 'sa'
    begin
        raiserror(15405, -1, -1, @loginame)
        return (1)
    end

    if @sid is null
    begin
        if @status = 0
            raiserror(15007,-1,-1,@loginame)
        else
            raiserror(15401,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin
        -- ERROR IF LOGIN IS ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
        begin
            raiserror(15022,-1,-1)
            return (1)
        end

        -- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME --
        if (not user_sid(user_id(@name_in_db)) = @sid)
        begin
            raiserror(15063,-1,-1)
            return (1)
        end

        -- ERROR IF LOGIN ALREADY HAS ACCESS --
        if exists (select sid from sysusers where sid = @sid and hasdbaccess = 1)
        begin
            if @status = 4
                raiserror(15024,-1,-1,@name_in_db)
            else
                raiserror(15023,-1,-1,@name_in_db)
            return (1)
        end

        -- GIVE DATABASE ACCESS TO THIS LOGIN --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where sid = @sid
        return @@error
    end

    if @name_in_db = 'sys'
        raiserror(15355,-1,-1)

    if user_id(@name_in_db) is not null OR
        @name_in_db IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        -- SYSUSERS NAME ALREADY EXISTS --
        if @status = 4
            raiserror(15024,-1,-1,@name_in_db)
        else
            raiserror(15023,-1,-1,@name_in_db)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
        select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @uid is null
    begin
        raiserror(15065,-1,-1)
        return (1)
    end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @uid, @status | 2, @name_in_db, @sid, 0x00, getdate(), getdate(), 0, NULL

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    -- PRINT SUCCESS --
    raiserror(15341,-1,-1, @loginame)

    -- RETURN SUCCESS STATUS --
    return @@error -- sp_grantdbaccess
go

create procedure sp_addalias
    @loginame       sysname,    -- name of the pretender
    @name_in_db     sysname     -- user to whom to alias the login
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @sid        varbinary(85),
            @targuid    smallint,
            @newuid     smallint,
            @status     smallint,
            @dbname     sysname

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_addalias')
        return (1)
    end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = CASE WHEN charindex('\', @loginame) > 0 THEN 12 ELSE 0 END
    if @status = 0
        select @sid = suser_sid(@loginame)          -- sql user
    -- retry sql user as nt with dflt domain
    if @sid is null
    begin
        select @sid = get_sid('\U'+@loginame, NULL) -- nt user
        if @sid is null
        begin
            if @status = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
        select @status = 12
    end
    -- PREVENT USE OF CERTAIN LOGINS --
    else if @sid = 0x1
    begin
        raiserror(15405, -1, -1, @loginame)
        return (1)
    end

    -- VALIDATE NAME-IN-DB (OBTAIN TARGET UID) --
    select @targuid = uid from sysusers where name = @name_in_db
                        and (issqluser = 1 or isntuser = 1)
                        and uid NOT IN (3,4)    -- INFORMATION_SCHEMA, system_function_schema
    if @targuid is null
    begin
        raiserror(15008,-1,-1,@name_in_db)
        return (1)
    end

    -- ERROR IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin

        -- ERROR IF ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
        begin
            raiserror(15022,-1,-1)
            return (1)
        end

        -- ERROR: LOGIN ALREADY A USER --
        select @name_in_db = name, @dbname = db_name() from sysusers where sid = @sid
        raiserror(15278,-1,-1,@loginame,@name_in_db,@dbname)
        return (1)
    end

    -- ALTER NAME TO AVOID CONFLICTS IN NAME SPACE --
    select @loginame = '\' + @loginame
    if user_id(@loginame) is not null
    begin
        raiserror(15023,-1,-1,@loginame)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @newuid = 5
    else
        select @newuid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @newuid is null
    begin
        raiserror(15065,-1,-1)
        return (1)
    end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @newuid, @status | 16, @loginame, @sid, 0x00,
                getdate(), getdate(), @targuid, NULL

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15340,-1,-1)
    return (0) -- sp_addalias
go

create procedure sp_addrole
    @rolename   sysname,        -- name of new role
    @ownername  sysname = 'dbo' -- name of owner of new role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @owner      smallint

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
        dbcc auditevent (111, 1, 0, NULL, NULL, @rolename, NULL)
        raiserror(15247,-1,-1)
        return (1)
    end
    else
    begin
        dbcc auditevent (111, 1, 1, NULL, NULL, @rolename, NULL)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_addrole')
        return (1)
    end

        -- RESOLVE OWNER NAME --
    select @owner = uid from sysusers where name = @ownername
                    and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own role
    if @owner is null
    begin
        raiserror(15008,-1,-1,@ownername)
        return (1)
    end

    -- VALIDATE ROLE NAME --
    execute @ret = sp_validname @rolename
    if @ret <> 0
        return (1)
    if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

    if @rolename = 'sys'
        raiserror(15355,-1,-1)

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null OR
        @rolename IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        if exists (select name from sysusers where issqlrole = 1 and name = @rolename)
            raiserror(15363,-1,-1,@rolename)
        else
            raiserror(15023,-1,-1,@rolename)

        return (1)
    end

    -- OBTAIN NEW ROLE UID (RESERVE 16384-16399) --
    if user_name(16400) IS NULL
        select @uid = 16400
    else
        select @uid = min(uid)+1 from sysusers
            where uid >= 16400 and uid < (32767 - 1)    -- stay in role range
                and user_name(uid+1) is null            -- uid not in use
    if @uid is null
    begin
        raiserror(15065,-1,-1)
        return (1)
    end

    -- INSERT THE ROW INTO SYSUSERS --
    insert into sysusers values
        (@uid, 0, @rolename, NULL, 0x00, getdate(), getdate(), @owner, NULL)

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15424,-1,-1)
    return (0) -- sp_addrole
go

create procedure sp_addapprole
    @rolename   sysname,        -- name of new app role
    @password   sysname         -- password for app role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @uid        smallint

    -- CHECK FOR NULL PASSWORD
    if (@password is null)
    begin
        raiserror(15034,-1,-1)
        return (1)
    end

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_addapprole')
        return (1)
    end

    -- VALIDATE APPROLE NAME --
    execute @ret = sp_validname @rolename
    if @ret <> 0
        return (1)
    if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

    if @rolename = 'sys'
        raiserror(15355,-1,-1)

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null OR
        @rolename IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        raiserror(15363,-1,-1,@rolename)
        return (1)
    end

    -- OBTAIN NEW APPROLE UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
        select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @uid is null
    begin
        raiserror(15065,-1,-1)
        return (1)
    end

    -- INSERT THE ROW INTO SYSUSERS --
    insert into sysusers values
        (@uid, 32, @rolename, NULL, 0x00, getdate(),
                    getdate(), 1, convert(varbinary(256), pwdencrypt(@password)))

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15425,-1,-1)
    return (0) -- sp_addapprole
go

CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @exec_stmt nvarchar(430)

    declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if suser_sid(@LoginName) = 0x1    -- 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15247,-1,-1)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits!
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
            raiserror(15063,-1,-1)
            return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
    select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
        if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, Null, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate() where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go

create proc sp_fulltext_table
    @tabname    nvarchar(517),
    @action        varchar(50),
    @ftcat        sysname = NULL,        -- create: catalog name
    @keyname    sysname = NULL        -- create: name of unique index
as
    declare @schemamodified int
    -- FULLTEXT MUST BE ACTIVE IN DATABASE --
    if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
    begin
        raiserror(15601,-1,-1)
        return 1
    end

    -- VALIDATE PARAMS --
    if @action is null
        OR @action not in ('create','drop','activate','deactivate',
            'start_change_tracking', 'stop_change_tracking',
            'start_background_updateindex', 'stop_background_updateindex',
            'update_index', 'start_full', 'start_incremental', 'stop')
        OR (@action not in ('create') and (@ftcat is not null or @keyname is not null))
        OR (@action in ('create') and (@ftcat is null or @keyname is null))
    begin
        raiserror(15600,-1,-1,'sp_fulltext_table')
        return 1
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_table')
        return 1
    end

    -- VALIDATE TABLE NAME --
    --  (1) Must exist in current database
    declare @objid int
    select @objid = object_id(@tabname, 'local')
    if @objid is null
    begin
        declare @curdbname sysname
        select @curdbname = db_name()
        raiserror(15009,-1,-1 ,@tabname, @curdbname)
        return 1
    end
    --  (2) Must be a user table (and not a temp table)
    if ObjectProperty(@objid, 'IsUserTable') = 0 OR substring(parsename(@tabname,1),1,1) = '#'
    begin
        raiserror(15218,-1,-1 ,@tabname)
        return 1
    end

    -- CHECK PERMISSION ON TABLE --
    if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
        AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

    -- CHECK DATABASE MODE (must not be read-only) --
    if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
    begin
        raiserror(15635, -1, -1, 'sp_fulltext_table')
        return 1
    end

    -- BEGIN TRAN AND LOCK TABLE --
    begin tran
    dbcc lockobjectschema(@tabname)
    if @@error <> 0
    begin
        goto error_abort_exit
    end

    -- OBTAIN CATALOG NAME FROM SYSOBJECTS & CHECK ACTION --
    declare @ftcatid smallint
    select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')
    if @ftcatid <> 0 and @action = 'create'
    begin
        raiserror(15605,-1,-1,@tabname)
        goto error_abort_exit
    end
    if @ftcatid = 0 and @action <> 'create'
    begin
        raiserror(15606,-1,-1,@tabname)
        goto error_abort_exit
    end

    if @action = 'create'
    begin
        -- CHECK CATALOG NAME --
        select @ftcatid = null
        select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
        if @ftcatid is null
        begin
            raiserror(7641,-1,-1,@ftcat)
            goto error_abort_exit
        end

        -- CHECK INDEX NAME (UNIQUE, SINGLE-KEY, 450-byte MAX, NON-NULLABLE) AND SET BIT IF FOUND --
        if IndexProperty(@objid, @keyname, 'IsUnique') = 1 and
           IndexProperty(@objid, @keyname, 'UserKeyCount') = 1 and
           IndexProperty(@objid, @keyname, 'IsHypothetical') = 0 and
           exists (select * from syscolumns where id = @objid and name = Index_col(@tabname, IndexProperty(@objid, @keyname, 'IndexId'), 1)
                and length <= 450 and isnullable = 0)
        begin
            update sysindexes set status = status | 33554432 where id = @objid
                and name = @keyname and indid > 0 and indid < 255
        end
        else
        begin
            raiserror(15607,-1,-1,@keyname)
            goto error_abort_exit
        end

        -- ADD CATALOG NAME TO SYSOBJECTS --
        update sysobjects set ftcatid = @ftcatid where id = @objid

        -- ADD TO CATALOG
        DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )    -- FTAddURL( @ftcatid, db_id(), @objid )
        if @@error <> 0
            goto error_abort_exit

    end

    if @action = 'drop'
    begin
        -- DROP FROM CATALOG (NO ERROR IF ALREADY DROPPED) --
        DBCC CALLFULLTEXT ( 6, @ftcatid, @objid )    -- FTDropURL( @ftcatid, db_id(), @objid )
        if @@error <> 0
            goto error_abort_exit

        -- DELETE SYSDEPENDS ENTRIES FOR IMAGE COLUMNS, IF ANY --
        delete sysdepends where [id] = @objid and
                          depid = @objid and
                          deptype = 1 and
                          number in ( select colid from syscolumns where [id] = @objid and
                                                                         type = 34 and
                                                                         (colstat & 16) = 16 )

        -- REMOVE CATALOG NAME AND BITS FROM SYSTEM TABLES --
        update syscolumns set colstat = colstat & ~80, language = 0 where [id] = @objid
        update sysindexes set status = status & ~33554432 where [id] = @objid
        update sysobjects set status = status & ~200, ftcatid = 0 where [id] = @objid

        -- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
        delete sysfulltextnotify where tableid = @objid

    end

    if @action = 'activate'
    begin

        -- MUST HAVE AT LEAST ONE COLUMN MARKED FOR FULLTEXT INDEXING --
        if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
        begin
            raiserror(15609, -1,-1,@tabname)
            goto error_abort_exit
        end

        -- NO ERROR IF INDEXING ALREADY ACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
        begin
            rollback tran
            return 0
        end


        update sysobjects set status = status | 8 where id = @objid

        if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
        begin

            -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
            if @@error <> 0
                goto error_abort_exit

            -- DELETE SYSFULLTEXTNOTIFY ENTRIES
            delete sysfulltextnotify where tableid = @objid

            -- START A FULL CRAWL FOR THE TABLE
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
            if @@error <> 0
                goto error_abort_exit

        end


    end

    if @action = 'deactivate'
    begin
        -- NO ERROR IF INDEXING ALREADY DEACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
        begin
            rollback tran
            return 0
        end

        -- IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
        begin
            -- SET STATE TO INACTIVE, SCHEMA-MODIFIED
            update sysobjects set status = ((status & ~72) | 128) where id = @objid
        end
        else
        begin
            -- SET STATE TO INACTIVE
            update sysobjects set status = (status & ~8) where id = @objid
        end

        -- DELETE SYSFULLTEXTNOTIFY ENTRIES
        delete sysfulltextnotify where tableid = @objid

        -- STOP EXISTING CRAWL (IMPLICIT STOP WITH WARNING)
        DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
        if @@error <> 0
            goto error_abort_exit


    end

    if @action = 'start_change_tracking'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF TABLE IS ALREADY ENABLED FOR NOTIFICATIONS --
        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
        begin
            raiserror(15631,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF DATABASE IS IN SINGLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15637, -1, -1, @tabname)
            goto error_abort_exit
        end

        -- CHECK TO SEE IF THERE ARE ANY COLUMNS WHICH ARE NOT IN ROW BLOBS --
        if (select count(*) from syscolumns where
            (id = object_id(@tabname)) and ((xtype = 34) or (xtype = 35) or (xtype = 99)) and
            ((colstat & 16) != 0) and (length = 16)) > 0
        begin
            raiserror(15639, -1, -1, @tabname)
        end

        -- STOP EXISTING CRAWL
        DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
        if @@error <> 0
            goto error_abort_exit

        -- DELETE SYSFULLTEXTNOTIFY ENTRIES
        delete sysfulltextnotify where tableid = @objid

        select @schemamodified = ObjectProperty(@objid, 'TableIsFulltextSchemaModified')

        -- SET TABLE TO CT ON. SCHEMA MOD. OFF --
        update sysobjects set status = ((status & ~128) | 64) where id = @objid

        -- COMMIT TRAN -- NESCESSARY TO TURN ON CT BEFORE CRAWL IS KICKED OFF --
        commit tran
        if @@error <> 0
            goto error_abort_exit

        if (@schemamodified = 1)
        begin
            -- START A FULL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
            if @@error <> 0
            begin
                -- NEED TO RUN A FULL POPULATION
                raiserror(15644, -1, -1,@tabname, 'start_full')
                return 1
            end
        end
        else
        begin
            -- START AN INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
            if @@error <> 0
            begin
                -- NEED TO RUN AN INCREMENTAL POPULATION
                raiserror(15644, -1, -1, @tabname, 'start_incremental')
                return 1
            end
        end
        return 0

    end

    if @action = 'stop_change_tracking'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
        begin
            rollback tran
                return 0
        end

        if(ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
        begin
            raiserror(7640,-1,-1, @tabname)
        end

        -- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
        DBCC CALLFULLTEXT ( 9, @objid )    -- FTDisableNotify( db_id(), @objid )
        if @@error <> 0
            goto error_abort_exit

        -- TURN OFF ACTIVE BITS IN SYSOBJECTS --
        update sysobjects set status = status & ~192 where id = @objid

        if ((select count(*) from sysfulltextnotify where tableid = @objid) != 0)
        begin
            raiserror(7638,-1,-1, @tabname)
        end

        -- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
        delete sysfulltextnotify where tableid = @objid


    end

    if @action = 'start_background_updateindex'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end


        -- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
        begin
            raiserror(15632,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED (SHOULD NEVER HAPPEN)--
        if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
        begin
            raiserror(15640,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF TABLE IS ALREADY ENABLED FOR AUTO PROPAGATION --
        if ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn') = 1
        begin
            raiserror(15633,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ENABLE TABLE FOR FULLTEXT AUTO PROPAGATION --
        DBCC CALLFULLTEXT ( 10, @ftcatid, @objid ) -- FTEnableAutoProp( @ftcatid, db_id(), @objid )
        if @@error <> 0
            goto error_abort_exit

        -- TURN ON FULLTEXT AUTOPROPAGATION BIT IN SYSOBJECTS --
        update sysobjects set status = status | 128 where id = @objid
    end

    if @action = 'stop_background_updateindex'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        if (ObjectProperty(@objid, 'TableFullTextBackgroundUpdateIndexOn') = 0)
        begin
            rollback tran
            return 0
        end

        -- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
        DBCC CALLFULLTEXT ( 9, @objid )    -- FTDisableNotify( db_id(), @objid )
        if @@error <> 0
            goto error_abort_exit

        -- TURN OFF ACTIVE BITS IN SYSOBJECTS --
        update sysobjects set status = status & ~128 where id = @objid
    end

    if @action = 'update_index'
    begin

        -- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
        begin
            raiserror(15634,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF TABLE IS NOT ACTIVE ANY MORE --
        if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED -- THIS SHOULD NEVER HAPPEN
        if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
        begin
            raiserror(15640,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF DATABASE IS IN SIGNLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15637, -1, -1, @tabname)
            goto error_abort_exit
        end

        DBCC CALLFULLTEXT ( 11, @ftcatid, @objid )    -- FTStartPropagation( db_id(), @ftcatid, @objid )
        if @@error <> 0
            goto error_abort_exit
    end

    if @action = 'start_full'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF DATABASE IS IN SINGLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15637, -1, -1, @tabname)
            goto error_abort_exit
        end

        -- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
        if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
        begin
            raiserror(7636,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- DELETE SYSFULLTEXTNOTIFY ENTRIES
        delete sysfulltextnotify where tableid = @objid

        -- START A FULL POPULATION FOR THIS TABLE --
        DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
        if @@error <> 0
            goto error_abort_exit

        if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
        and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
            -- SET TABLE SCHEMA-UNMODIFIED
            update sysobjects set status = status & ~128 where id = @objid

    end

    if @action = 'start_incremental'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF DATABASE IS IN SINGLE USER MODE --
        if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
        begin
            raiserror(15637, -1, -1, @tabname)
            goto error_abort_exit
        end

        -- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
        if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
        begin
            raiserror(7636,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- DELETE SYSFULLTEXTNOTIFY ENTRIES
        delete sysfulltextnotify where tableid = @objid

        -- START AN INCREMENTAL POPULATION FOR THIS TABLE --
        if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
            and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
            begin
            -- FULL CRAWL IF SCHEMA MODIFIED
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
            if @@error <> 0
                goto error_abort_exit

            -- SET TABLE SCHEMA-UNMODIFIED
            update sysobjects set status = status & ~128 where id = @objid
        end
        else
        begin
            -- INCREMENTAL CRAWL
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
            if @@error <> 0
                goto error_abort_exit
        end
    end

    if @action = 'stop'
    begin
        -- ERROR IF TABLE IS NOT ACTIVATED --
        if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
        begin
            raiserror(15630,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
        if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
        and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
        or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
        begin
            raiserror(15642,-1,-1, @tabname)
            goto error_abort_exit
        end

        -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
        DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
        if @@error <> 0
            goto error_abort_exit
    end

    -- COMMIT TRAN --
    commit tran
    if @@error <> 0
        goto error_abort_exit

    -- SUCCESS --
    return 0

error_abort_exit:
    rollback tran
    return 1    -- sp_fulltext_table
go

create proc sp_fulltext_column
    @tabname        nvarchar(517),      -- table name
    @colname        sysname,            -- column name
    @action         varchar(20),        -- add | drop
    @language       int = null,         -- LCID of data in the column
    @type_colname   sysname = null      -- column name, valid if colname is img

as
    -- FULLTEXT MUST BE ACTIVE IN DATABASE --
    if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
    begin
        raiserror(15601,-1,-1)
        return 1
    end

    -- VALIDATE PARAMS --
    if @action is null or @action not in ('add','drop')
    begin
        raiserror(15600,-1,-1,'sp_fulltext_column')
        return 1
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_column')
        return 1
    end

    -- VALIDATE TABLE NAME --
    --    (1) Must exist in current database
    declare @objid int
    select @objid = object_id(@tabname, 'local')
    if @objid is null
    begin
        declare @curdbname sysname
        select @curdbname = db_name()
        raiserror(15009,-1,-1 ,@tabname, @curdbname)
        return 1
    end
    --    (2) Must be a user table
    if ObjectProperty(@objid, 'IsUserTable') = 0
    begin
        raiserror(15218,-1,-1 ,@tabname)
        return 1
    end

    -- CHECK PERMISSION ON TABLE --
    if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
        AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

    -- CHECK DATABASE MODE (must not be read-only) --
    if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
    begin
        raiserror(15635, -1, -1, 'sp_fulltext_column')
        return 1
    end

    -- BEGIN TRAN AND LOCK TABLE --
    begin tran
    dbcc lockobjectschema(@tabname)
    if @@error <> 0
    begin
        goto error_abort_exit
    end

    -- CHECK FOR CATALOG IN SYSOBJECTS --
    declare @ftcatid smallint
    select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')

    if @ftcatid = 0
    begin
        raiserror(15606,-1,-1,@tabname)
        goto error_abort_exit
    end

    -- VALIDATE COLUMN NAME (CANNOT BE COMPUTED) --
    declare @typename sysname
    select @typename = type_name(ColumnProperty(@objid, @colname, 'SystemType'))
    if @typename is null OR ColumnProperty(@objid, @colname, 'IsComputed') = 1
    begin
        raiserror(15104,-1,-1,@tabname,@colname)
        goto error_abort_exit
    end

    -- VALIDATE PARAMETERS
    if (@action <> 'add' or @typename <> N'image') and @type_colname is not null
    begin
        raiserror(15600, -1, -1, 'sp_fulltext_column')
        goto error_abort_exit
    end

    if @action = 'add'
    begin
        -- VALIDATE COLUMN TYPE --
        if @typename not in (N'nchar',N'nvarchar',N'ntext',N'char',N'varchar',N'text', N'image')
        begin
            raiserror(15611,-1,-1,@colname,@tabname)
            goto error_abort_exit
        end

        -- LANGUAGE
        if @language is null
            begin
                -- USE THE SERVER DEFAULT WORD BREAKING LANGUAGE
                select @language = value from master.dbo.syscurconfigs where config = 1126
            end
        else
            begin
                -- VALIDATE @LANGUAGE ARGUMENT
                if @language < 0
                begin
                    raiserror(15600,-1,-1,'sp_fulltext_column')
                    goto error_abort_exit
                end
            end

        update syscolumns set language = @language where id = @objid and name = @colname

        -- IF TABLE HAS ZERO INDEXED COLUMNS (THIS IS THE FIRST COLUMN TO BE ADDED), MARK IT ACTIVE
        if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
            and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
            and (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0)
        begin
            update sysobjects set status = (status  | 8) where id = @objid
        end

        -- SET THE BIT FOR THIS COLUMN --
        update syscolumns set colstat = colstat | 16 where id = @objid and name = @colname

        -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
        DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
        if @@error <> 0
            goto error_abort_exit

        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
        begin


            if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
            begin

                -- DELETE SYSFULLTEXTNOTIFY ENTRIES
                delete sysfulltextnotify where tableid = @objid

                -- START A FULL CRAWL FOR THE TABLE
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
                if @@error <> 0
                    goto error_abort_exit
            end

        end
        else
        begin

            -- SET STATE TO SCHEMA_MODIFIED
            update sysobjects set status = ((status & ~64) | 128) where id = @objid
        end

        if @typename = N'image'
        begin
            -- VALIDATE THAT THE TYPE COLUMN IS GIVEN AND THAT IT IS VALID
            if @type_colname is null
            begin
                raiserror(15600, -1, -1, 'sp_fulltext_column')
                goto error_abort_exit
            end

            declare @typecolname sysname
            select @typecolname = type_name(ColumnProperty(@objid, @type_colname, 'SystemType'))

            -- TYPE COLUMN HAS TO BE A CHARACTER COLUMN
            if @typecolname not in (N'nchar',N'nvarchar',N'char',N'varchar')
            begin
                raiserror(15600 , -1, -1, 'sp_fulltext_column')
                goto error_abort_exit
            end

            -- ADD ENTRY OF COLID IN SYSDEPENDS
            declare @colid smallint
            declare @type_colid smallint

            select @colid = colid from syscolumns where [id] = @objid and name = @colname
            select @type_colid = colid from syscolumns where [id]  = @objid and name = @type_colname

            if not exists ( select [id] from sysdepends
                            where  [id] = @objid and
                                   depid = @objid and
                                   number = @colid )
            begin
                insert into sysdepends ([id], depid, number, depnumber, status, deptype )
                        values( @objid, @objid, @colid, @type_colid, 0, 1)
            end

            -- SET BIT INDICATING TYPE COLUMN
            update syscolumns set colstat = colstat | 64 where id = @objid and name = @type_colname
        end

    end
    else
    begin
        -- CLEAR THE BIT & ZERO LCID FOR THIS COLUMN --
        update syscolumns set colstat = colstat & ~16, language = 0
            where id = @objid and name = @colname

        -- IF LAST COLUMN DROPPED
        if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
        begin
            -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
            if @@error <> 0
                goto error_abort_exit

            -- IF TABLE HAS NOT BEEN DEACTIVATED
            if ((ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1)
                or (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0))
            begin

                -- IF CHANGE-TRACKING IS OFF
                if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
                begin
                    -- SET TABLE TO SCHEMA UNMODIFIED, INACTIVE (TURN OFF ALL BITS)
                    update sysobjects set status = (status & ~200) where id = @objid
                end
                else
                begin
                    -- SET TABLE TO INACTIVE
                    update sysobjects set status = (status & ~8) where id = @objid
                end
            end
        end
        else
        if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
        begin

            -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
            if @@error <> 0
                goto error_abort_exit

            if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
            begin
                -- DELETE SYSFULLTEXTNOTIFY ENTRIES
                delete sysfulltextnotify where tableid = @objid

                -- START A FULL CRAWL FOR THE TABLE
                DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
                if @@error <> 0
                    goto error_abort_exit
            end

        end
        else
        begin

            -- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
            DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
            if @@error <> 0
                goto error_abort_exit

            -- SET BITS IN SYSOBJECTS
            update sysobjects set status = ((status & ~64) | 128) where id = @objid
        end

        -- IF IMAGE COLUMN, UNBIND FROM THE TYPE COLUMN
        if @typename = N'image'
        begin
            declare @colid1 smallint
            declare @type_colid1 smallint

            select @colid1 = colid from syscolumns where [id] = @objid and name = @colname
            select @type_colid1 = depnumber from sysdepends
                    where [id] = @objid and
                         depid = @objid and
                         number = @colid1

            delete sysdepends where [id] = @objid and
                                    depid = @objid and
                                    number = @colid1 and
                                    depnumber = @type_colid1 and
                                    deptype = 1

            -- CLEAR BIT RELATING THE IMAGE COLUMN AND TYPE COLUMN
            if not exists ( select depnumber from sysdepends
                            where    [id] = @objid and
                                    depnumber = @type_colid1 and
                                    deptype = 1 and
                                    number in (select colid from syscolumns
                                             where    [id] = @objid and
                                                    type = 34 and
                                                    (colstat & 16) = 16) )
            begin
                update syscolumns set colstat = colstat & ~64 where [id] = @objid and colid = @type_colid1
            end
        end

    end

    -- COMMIT TRAN --
    commit tran
    if @@error <> 0
        goto error_abort_exit

    -- SUCCESS --
    return 0

error_abort_exit:
    rollback tran
    return 1    -- sp_fulltext_column
go

create procedure sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

declare @dbname sysname
,@BlankSpaceAdded   int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int --lengths of line feed carriage return
,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText    nvarchar(4000)
,@Line          nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(LineId    int
 ,Text  nvarchar(255) collate database_default)

/*
**  Make sure the @objname is local to the current database.
*/
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
        begin
                raiserror(15250,-1,-1)
                return (1)
        end

/*
**  See if @objname exists.
*/
if (object_id(@objname) is null)
        begin
        select @dbname = db_name()
        raiserror(15009,-1,-1,@objname,@dbname)
                return (1)
        end

-- If second parameter was given.
if ( @columnname is not null)
    begin
        -- Check if it is a table
        if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
            begin
                raiserror(15218,-1,-1,@objname)
                return(1)
            end
        -- check if it is a correct column name
        if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
            begin
                raiserror(15645,-1,-1,@columnname)
                return(1)
            end
    if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
        begin
            raiserror(15646,-1,-1,@columnname)
            return(1)
        end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
                        (select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
                        order by number,colid
        FOR READ ONLY

    end
else
    begin
        /*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
            and o.id = c.id and o.id = object_id(@objname)) = 0
                begin
                        raiserror(15197,-1,-1,@objname)
                        return (1)
                end

        if (select count(*) from syscomments where id = object_id(@objname)
            and encrypted = 0) = 0
                begin
                        raiserror(15471,-1,-1)
                        return (0)
                end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
                ORDER BY number, colid
        FOR READ ONLY
    end

/*
**  Else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1


OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

    SELECT  @BasePos    = 1
    SELECT  @CurrentPos = 1
    SELECT  @TextLength = LEN(@SyscomText)

    WHILE @CurrentPos  != 0
    BEGIN
        --Looking for end of line followed by carriage return
        SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        --If carriage return found
        IF @CurrentPos != 0
        BEGIN
            /*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            BEGIN
                SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                INSERT #CommentText VALUES
                ( @LineId,
                  isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                SELECT @Line = NULL, @LineId = @LineId + 1,
                       @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
            END
            SELECT @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            SELECT @BasePos = @CurrentPos+2
            INSERT #CommentText VALUES( @LineId, @Line )
            SELECT @LineId = @LineId + 1
            SELECT @Line = NULL
        END
        ELSE
        --else carriage return not found
        BEGIN
            IF @BasePos <= @TextLength
            BEGIN
                /*If new value for @Lines length will be > then the
                **defined length
                */
                While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                BEGIN
                    SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    SELECT @Line = NULL, @LineId = @LineId + 1,
                        @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                END
                SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                BEGIN
                    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
                END
            END
        END
    END

    FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
    INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE  ms_crs_syscom
DEALLOCATE     ms_crs_syscom

DROP TABLE     #CommentText

return (0) -- sp_helptext
go

grant execute on sp_droplogin to public
grant execute on sp_addsrvrolemember to public
grant execute on sp_dropsrvrolemember to public
grant execute on sp_grantdbaccess to public
grant execute on sp_addalias to public
grant execute on sp_addrole to public
grant execute on sp_addapprole to public
grant execute on sp_change_users_login to public
grant execute on sp_fulltext_table to public
grant execute on sp_fulltext_column to public
grant execute on sp_helptext to public
go

--------------------------------------------------------------------------------
--    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------

if (exists (select * from sysobjects
        where name = 'sp_tableswc' and type = 'P '))
    drop proc sp_tableswc
go

print 'creating sp_tableswc'
go

create procedure sp_tableswc(
               @table_name        nvarchar(384)    = null,
               @table_owner     nvarchar(384)    = null,
               @table_qualifier sysname    = null,
               @table_type        varchar(100) = null)
as
    declare @databasename    sysname
    declare @qualprocname    nvarchar(141) /* 128 + '..sp_tables' */

    create table #sptables (
        TABLE_QUALIFIER sysname collate database_default null,
        TABLE_OWNER sysname collate database_default null,
        TABLE_NAME sysname collate database_default null,
        TABLE_TYPE    varchar(32) collate database_default null,
        REMARKS varchar(254) collate database_default null)

    declare databases CURSOR FOR
        select name from master..sysdatabases
        where name like @table_qualifier and name <> 'model' and has_dbaccess(name)=1
        for read only

    open databases
    fetch next from databases into @databasename
    while (@@FETCH_STATUS <> -1)
    begin
        if (charindex('%', @databasename) = 0)
        begin    /* skip dbnames w/wildcard characters to prevent loop */
            select @qualprocname = @databasename + '..sp_tables'
            insert into #sptables exec @qualprocname
                @table_name, @table_owner, @databasename, @table_type
        end
        fetch next from databases into @databasename
    end
    deallocate databases
    select * from #sptables
        order by 4, 1, 2, 3
go

grant execute on sp_tableswc to public
go

-- Since we are updating the contents of instcat.sql and therefore the contents
-- of the ODBC/OLEDB catalog procs, we need to update the SPROC version

update spt_server_info
    set attribute_value = '8.00.375'
    where attribute_id = 500
go

--------------------------------------------------------------------------------
--    End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go